Fric-frac MySQL DML CategoryEvent

Probleem

We moeten de gegevens van een event categorie kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de naam.

Design

Op basis van het Fric-frac Event Calendar logisch model maken we de stored procedures. Naast de standaard stored procedures, Insert, Update, Delete, SelectOne, SelectAll, maken we voor elke tabelkolom waarbij de Searchable is ingesteld op SELECTBY maken we een stored procedure waarbij er gezocht kan worden op deze kolom in de tabel.

Oplossing

Vincent: Wij hebben dit gedaan met een reeks genummerde scripts die je snel na elkaar kan uitvoeren als er iets misloopt. Je vindt deze terug op Digitap, onder stored procedures EventTopic en EventCategory. Je wordt wel verwacht te kunnen toelichten wat deze scripts doen, dus lees ze aandachtig!

De Insert stored procedure

De Insert stored procedure is speciaal. We checken eerst als de naam al bestaat. Als die bestaat wordt een Id van -100 geretourneerd.

USE docent1;
DROP PROCEDURE IF EXISTS EventCategoryInsert;
DELIMITER //
CREATE PROCEDURE `EventCategoryInsert`
(     IN pName NVARCHAR (120) ,
    OUT pId INT
)
BEGIN
    IF NOT EXISTS (SELECT * from EventCategory WHERE `Name` = pName )
    THEN
        INSERT INTO `EventCategory`
        (
            `EventCategory`.`Name`
        )
        VALUES (
            pName
        );
        -- return the Id of the inserted row
        SELECT LAST_INSERT_ID() INTO pId;
    else
        set pId = -100; -- Name exitst already
    END IF;

END //
DELIMITER ;

De Insert stored procedure testen

De mogelijke waarden zijn:

Appearance or Signing
Attraction
Camp. Trip or Retreat
Concert or Performance
Conference
Convention
Course, Training or Workshop
Dinner or Gala
Festival or Fair
Game or Competition
Meeting or Networking Event
Other
Party or Social Gathering
Race or Endurance Event
Rally
Screening
Seminar or Talk
Tour
Tournament
Tradeshow, Consumer Show or Expo

We beginnen met één rij toe te voegen:

use docent1;
call EventCategoryInsert('Appearance or Signing', @newId);
select @newId;

De Id van de nieuwe rij wordt doorgegeven aan de @newId output parameter.

Als je de call nogmaals uitvoert wordt de waarde -100 geretourneerd!!!!

Je kan de andere categoriën ook toevoegen:

use docent1;
call EventCategoryInsert('Attraction Camp.', @newId);
call EventCategoryInsert('Trip or Retreat', @newId);
call EventCategoryInsert('Concert or Performance', @newId);
call EventCategoryInsert('Conference', @newId);
call EventCategoryInsert('Convention', @newId);
call EventCategoryInsert('Conference', @newId);
call EventCategoryInsert('Course, Training or Workshop', @newId);
call EventCategoryInsert('Dinner or Gala', @newId);
call EventCategoryInsert('Festival or Fair', @newId);
call EventCategoryInsert('Game or Competition', @newId);
call EventCategoryInsert('Meeting or Networking Event', @newId);
call EventCategoryInsert('Other', @newId);
call EventCategoryInsert('Party or Social Gathering', @newId);
call EventCategoryInsert('Race or Endurance Event', @newId);
call EventCategoryInsert('Rally', @newId);
call EventCategoryInsert('Screening', @newId);
call EventCategoryInsert('Seminar or Talk', @newId);
call EventCategoryInsert('Tour', @newId);
call EventCategoryInsert('Tournament', @newId);
call EventCategoryInsert('Tradeshow, Consumer Show or Expo', @newId);

We krijgen die foutmelding omdat we aan de kolom Name, de constraint UNIQUE hebben toegevoegd (zie Fric-frac MySQL Data Definition Language).

Na de dubbele rij stopt MySQL met de uitvoering van de script. Alle rijen na de dubbele zijn niet toegevoegd. We deleten tot en met de dubbele rij en proberen het opnieuw:

Let erop dat MySQL toch nieuwe Id's heeft gecreëerd bij de mislukte poging:

Als je toch een correcte nummering wilt, moet je eerste de rijen deleten die niet in de juiste volgorde staan, de teller resetten op de laatste rij in de juiste volgorde, en de gedelete rijen weer toevoegen:

use docent1;
delete from EventCategory where Id > 6;
-- reset de teller:
ALTER TABLE EventCategory AUTO_INCREMENT = 6;
-- en voeg de rijen opnieuw toe:
-- call EventCategoryInsert('Attraction Camp.', @newId);
-- call EventCategoryInsert('Trip or Retreat', @newId);
-- call EventCategoryInsert('Concert or Performance', @newId);
-- call EventCategoryInsert('Conference', @newId);
-- call EventCategoryInsert('Convention', @newId);
-- call EventCategoryInsert('Conference', @newId);
call EventCategoryInsert('Course, Training or Workshop', @newId);
call EventCategoryInsert('Dinner or Gala', @newId);
call EventCategoryInsert('Festival or Fair', @newId);
call EventCategoryInsert('Game or Competition', @newId);
call EventCategoryInsert('Meeting or Networking Event', @newId);
call EventCategoryInsert('Other', @newId);
call EventCategoryInsert('Party or Social Gathering', @newId);
call EventCategoryInsert('Race or Endurance Event', @newId);
call EventCategoryInsert('Rally', @newId);
call EventCategoryInsert('Screening', @newId);
call EventCategoryInsert('Seminar or Talk', @newId);
call EventCategoryInsert('Tour', @newId);
call EventCategoryInsert('Tournament', @newId);
call EventCategoryInsert('Tradeshow, Consumer Show or Expo', @newId);
select * from EventCategory order by Id;

De Update stored procedure

De Update stored procedure gelijkt op de Insert. Maar hier geef je de Id mee van de rij die je wilt updaten.

USE docent1;
DROP PROCEDURE IF EXISTS EventCategoryUpdate;
DELIMITER //
CREATE PROCEDURE `EventCategoryUpdate`
(
	pName NVARCHAR (120) ,
	pId INT 
)
BEGIN
UPDATE `EventCategory`
	SET
		`Name` = pName
	WHERE `EventCategory`.`Id` = pId;
END //
DELIMITER ;

De Update stored procedure testen:

use docent1;
-- we vervangen 'Conference' foor 'Oudeleerlingenavond'
call EventCategoryUpdate('Oudeleerlingenavond', 5);
select * from EventCategory order by Id;

En we zetten de oorspronkelijke waarde weer terug:

use docent1;
-- we vervangen 'Oudeleerlingenavond' foor 'Conference'
call EventCategoryUpdate('Conference', 5);
select * from EventCategory order by Id;

De Delete stored procedure

We maken eerste de stored procedure:

USE docent1;
DROP PROCEDURE IF EXISTS EventCategoryDelete;
DELIMITER //
CREATE PROCEDURE `EventCategoryDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `EventCategory`
	WHERE `EventCategory`.`Id` = pId;
END //
DELIMITER ;

De Delete stored procedure testen

use docent1;
-- we deleted de rij 'Conference'
call EventCategoryDelete(5);
select * from EventCategory order by Id;

We voegen categorie opnieuw toe:

use docent1;
-- we deleted de rij 'Conference'
call EventCategoryInsert('Conference', @newId);
select @newId;

De SelectOne stored procedure

De SelectOne stored procedure haalt één rij uit de tabel op op basis van de meegegeven Id.

USE docent1;
DROP PROCEDURE IF EXISTS EventCategorySelectOne;
DELIMITER //
CREATE PROCEDURE `EventCategorySelectOne`
(
	 pId INT 
)
BEGIN
SELECT `EventCategory`.`Name`,
	`EventCategory`.`Id`
 
FROM `EventCategory`
	WHERE `EventCategory`.`Id` = pId;
END //
DELIMITER ;

De SelectOne stored procedure testen

use docent1;
-- we selecteren de rij 'Conference' met Id=5
call EventCategorySelectOne(1);

De SelectAll stored procedure

We moeten in Fric-frac Event Calendar logisch model gaan kijken om te weten te komen welke kolommen getoond moeten worden. In de List kolom staat YES als de kolom in de SelectAll stored procedure moet worden opgenomen.

USE docent1;
DROP PROCEDURE IF EXISTS EventCategorySelectAll;
DELIMITER //
CREATE PROCEDURE `EventCategorySelectAll`
(
)
BEGIN
SELECT `EventCategory`.`Name`,
	`EventCategory`.`Id`
	FROM `EventCategory`
	ORDER BY `Name`;
END //
DELIMITER ;

De SelectAll stored procedure testen

use docent1;
-- we selecteren alle rijen
call EventCategorySelectAll();

De SelectByName stored procedure

We maken eerst de stored procedure:

USE docent1;
DROP PROCEDURE IF EXISTS EventCategorySelectByName;
DELIMITER //
CREATE PROCEDURE `EventCategorySelectByName`
(
	 pName NVARCHAR (120) 
)
BEGIN
SELECT `EventCategory`.`Name`,
	`EventCategory`.`Id`

	FROM `EventCategory`
	WHERE `EventCategory`.`Name` = pName
	ORDER BY `EventCategory`.`Name`;
END //
DELIMITER ;

De SelectByName stored procedure testen

use docent1;
-- we selecteren alle rijen
call EventCategorySelectByName('Conference');

De SelectLikeName stored procedure

Met de SelectLikeName procedure kan je alle categoriën ophalen die beginnen met een bepaalde tekenreeks. We gebruiken daarvoor de like operator en we plakken aan het einde van de zoektekenreeks een %:

USE docent1;
DROP PROCEDURE IF EXISTS EventCategorySelectLikeName;
DELIMITER //
CREATE PROCEDURE `EventCategorySelectLikeName`
(
	pName NVARCHAR (120) 
)
BEGIN
SELECT `EventCategory`.`Name`,
	`EventCategory`.`Id`
 
	FROM `EventCategory`
	WHERE `EventCategory`.`Name` like CONCAT(pName, '%')
	ORDER BY `EventCategory`.`Name`;
END //
DELIMITER ;

De SelectLikeName stored procedure testen

We willen alle categorieën die beginnen met 'con':

use docent1;
-- we selecteren alle rijen
call EventCategorySelectLikeName('Con');

De SelectLikeX stored procedure

We willen alle categoriën kunnen ophalen waarin een bepaalde tekenreeks voorkomt. We gebruiken daarvoor de like operator en we sluiten de zoektekenreeks in tussen twee %:

USE docent1;
DROP PROCEDURE IF EXISTS EventCategorySelectLikeXName;
DELIMITER //
CREATE PROCEDURE `EventCategorySelectLikeXName`
(
	pName NVARCHAR (120) 
)
BEGIN
	SELECT `EventCategory`.`Name`,
	`EventCategory`.`Id`

	FROM `EventCategory`
	WHERE `EventCategory`.`Name` like CONCAT('%', pName, '%')

	ORDER BY `EventCategory`.`Name` ;
END //
DELIMITER ;

De SelectLikeX stored procedure uittesten

We willen alle categorieën ophalen waarin de tekenreeks 'con' in voorkomt:

use docent1;
-- we selecteren alle rijen
call EventCategorySelectLikeXName('con');